package com.bj58.groupbuy.action.charts;

import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.bj58.groupbuy.action.db.CommonDao;
import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.model.BelongLocal;
import com.bj58.groupbuy.action.model.ChinahrIndustry;
import com.bj58.groupbuy.action.model.ChinahrJob;
import com.bj58.groupbuy.action.model.ChinahrSource;
import com.bj58.groupbuy.action.model.DisplayLocal;
import com.bj58.groupbuy.action.model.InfoSourcePlatform;
import com.bj58.groupbuy.action.model.JxedtFlowSource;
import com.bj58.groupbuy.action.model.NetFlowSource;
import com.bj58.groupbuy.action.model.NewBelongCate;
import com.bj58.groupbuy.action.model.NewDisplayCate;
import com.bj58.groupbuy.action.util.MyStore;

public class ChartService {
	private CommonDao  comDao = new CommonDao();
	

	/**
	 * 归属类别
	 */
	public List<NewBelongCate> getAllBLBusinessType(String showBusi, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		String business_type_name = "business_type_name";
		if(isBiz){
			business_type = business_type + "_biz";
			business_type_name = business_type_name + "_biz";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select  *  ");
		sql.append(" from data_dict.m_common_belong_cate ");
		sql.append(" where  "+business_type_name+" is not null ");
		
		if(!"".equals(showBusi) && null != showBusi){
			if(!"".equals(showBusi) && null != showBusi){
				String busis[] =  showBusi.split(",");
				sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
			}
		}
		sql.append(" group by "+business_type_name);
		
		List<NewBelongCate> rsList = comDao.getAllColsByVo(sql.toString(), NewBelongCate.class, DBConfig.db_131_data_bi.getIntType());
		
		return rsList;
	}

	
	public List<NewBelongCate> getBLLevelOne(String businesTypeName,
			String cateOne, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		if(isBiz){
			business_type = business_type + "_biz";
		}
		if(businesTypeName.length()!=businesTypeName.getBytes().length)
		{
			business_type="business_type_name";
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *  ");
		sql.append(" FROM data_dict.m_common_belong_cate ");
		sql.append(" where cate2_name is not null  ");

		if(!StringUtils.isBlank(cateOne) && !"null".equals(cateOne)){
			String cates[] =  cateOne.split(",");
			sql.append(" and cate1_name  in('" + StringUtils.join(cates, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(businesTypeName) && !"null".equals(businesTypeName)){
			String busis[] =  businesTypeName.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}
		
		sql.append(" group by  cate1_name");
		
		return comDao.getAllColsByVo(sql.toString(), NewBelongCate.class, DBConfig.db_131_data_bi.getIntType());
	}

	
	public List<NewBelongCate> getBLLevelTwo(String businesTypeName,
			String cateOne, String cateTwo, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		if(isBiz){
			business_type = business_type + "_biz";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *  ");
		sql.append(" FROM data_dict.m_common_belong_cate ");
		sql.append(" where cate2_name is not null ");
		if(!StringUtils.isBlank(businesTypeName) && !"null".equals(businesTypeName)){
			String busis[] =  businesTypeName.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}

		if(!StringUtils.isBlank(cateOne) && !"null".equals(cateOne)){
			String cates[] =  cateOne.split(",");
			sql.append(" and cate1_name  in('" + StringUtils.join(cates, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(cateTwo) && !"null".equals(cateTwo)){
			String cateTwoArr[] =  cateTwo.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		
		sql.append(" group by  cate2_name");
		
		return comDao.getAllColsByVo(sql.toString(), NewBelongCate.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	
	public List<NewBelongCate> getBLLevelThree(String businesTypeName,
			String cateOne, String cateTwo, String cateThree, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		if(isBiz){
			business_type = business_type + "_biz";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *  ");
		sql.append(" FROM data_dict.m_common_belong_cate ");
		sql.append(" where cate3_name is not null ");
		if(!StringUtils.isBlank(businesTypeName) && !"null".equals(businesTypeName)){
			String busis[] =  businesTypeName.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(cateOne) && !"null".equals(cateOne)){
			String cates[] =  cateOne.split(",");
			sql.append(" and cate1_name  in('" + StringUtils.join(cates, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(cateTwo) && !"null".equals(cateTwo)){
			String cateTwoArr[] =  cateTwo.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(cateThree) && !"null".equals(cateThree)){
			String cateTwoArr[] =  cateThree.split(",");
			sql.append(" and  cate3_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		
		sql.append(" group by  cate3_name");
		
		return comDao.getAllColsByVo(sql.toString(), NewBelongCate.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	
	/**
	 * 展现类别
	 */
	public List<NewDisplayCate> getAllBusinessType(String showBusi, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		String business_type_name = "business_type_name";
		if(isBiz){
			business_type = business_type + "_biz";
			business_type_name = business_type_name + "_biz";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select  *  ");
		sql.append(" from data_dict.m_common_disp_cate ");
		sql.append(" where  "+business_type_name+" is not null ");
		
		if(!"".equals(showBusi) && null != showBusi){
			String busis[] =  showBusi.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}
		sql.append(" group by "+business_type_name);
		
		List<NewDisplayCate> rsList = comDao.getAllColsByVo(sql.toString(), NewDisplayCate.class, DBConfig.db_131_data_bi.getIntType());
		
		return rsList;
	}

	public List<NewDisplayCate> getLevelOne(String businesTypeName,
			String cateOne, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		if(isBiz){
			business_type = business_type + "_biz";
		}
		if(businesTypeName.length()!=businesTypeName.getBytes().length)
		{
			business_type="business_type_name";
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *  ");
		sql.append(" FROM data_dict.m_common_disp_cate ");
		sql.append(" where cate1_name is not null  ");

		if(!StringUtils.isBlank(cateOne) && !"null".equals(cateOne)){
			String cates[] =  cateOne.split(",");
			sql.append(" and cate1_name  in('" + StringUtils.join(cates, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(businesTypeName) && !"null".equals(businesTypeName)){
			String busis[] =  businesTypeName.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}
		
		sql.append(" group by  cate1_name");
		
		return comDao.getAllColsByVo(sql.toString(), NewDisplayCate.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	
	public List<NewDisplayCate> getLevelTwo(String businesTypeName,
			String cateOne, String cateTwo, boolean  isBiz) throws Exception {
		String business_type = "business_type";
		if(isBiz){
			business_type = business_type + "_biz";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *  ");
		sql.append(" FROM data_dict.m_common_disp_cate ");
		sql.append(" where cate2_name is not null ");
		if(!StringUtils.isBlank(businesTypeName) && !"null".equals(businesTypeName)){
			String busis[] =  businesTypeName.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}

		if(!StringUtils.isBlank(cateOne) && !"null".equals(cateOne)){
			String cates[] =  cateOne.split(",");
			sql.append(" and cate1_name  in('" + StringUtils.join(cates, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(cateTwo) && !"null".equals(cateTwo)){
			String cateTwoArr[] =  cateTwo.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		
		sql.append(" group by  cate2_name");
		
		return comDao.getAllColsByVo(sql.toString(), NewDisplayCate.class, DBConfig.db_131_data_bi.getIntType());
	}


	public List<NewDisplayCate> getLevelThree(String businesTypeName, String cateOne, String cateTwo,String cateThree, boolean  isBiz)
			throws Exception {
		String business_type = "business_type";
		if(isBiz){
			business_type = business_type + "_biz";
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *  ");
		sql.append(" FROM data_dict.m_common_disp_cate ");
		sql.append(" where cate3_name is not null ");
		if(!StringUtils.isBlank(businesTypeName) && !"null".equals(businesTypeName)){
			String busis[] =  businesTypeName.split(",");
			sql.append(" and "+business_type+" in('" + StringUtils.join(busis, "','")+"') " );
		}

		if(!StringUtils.isBlank(cateOne) && !"null".equals(cateOne)){
			String cates[] =  cateOne.split(",");
			sql.append(" and cate1_name  in('" + StringUtils.join(cates, "','")+"') " );
		}
		
		if(!StringUtils.isBlank(cateTwo) && !"null".equals(cateTwo)){
			String cateTwoArr[] =  cateTwo.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		if(!StringUtils.isBlank(cateThree) && !"null".equals(cateThree)){
			String cateTwoArr[] =  cateThree.split(",");
			sql.append(" and  cate3_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		
		sql.append(" group by  cate3_name");
		
		return comDao.getAllColsByVo(sql.toString(), NewDisplayCate.class, DBConfig.db_131_data_bi.getIntType());
	}
	

	/*
	 * 归属城市
	 */
	public List<BelongLocal> getBelongLevelOne(String city1) throws Exception {
		StringBuilder sql = new StringBuilder("select * from data_dict.m_common_belong_city " +
				"where city2_name is null and city_id >0 ");
		if(!StringUtils.isBlank(city1) && !"null".equals(city1)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city1_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		sql.append(" group by city1_name ");	
		sql.append(" order by city_name_en ");	
		return comDao.getAllColsByVo(sql.toString(), BelongLocal.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<BelongLocal> getBelongLevelTwo(String city1, String city2) throws Exception {
		StringBuilder sql = new StringBuilder("SELECT * FROM data_dict.m_common_belong_city  where city2_name is not null and city3_name is null");
		if(!StringUtils.isBlank(city1) && !"null".equals(city1)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city1_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		if(!StringUtils.isBlank(city2) && !"null".equals(city2)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}	
			
		sql.append(" group by city2_name ");
		sql.append("order by city_name_en ");	
		return comDao.getAllColsByVo(sql.toString(), BelongLocal.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<BelongLocal> getBelongLevelThree(String city1,
			String city2 ,String city3) throws Exception {
		StringBuilder sql = new StringBuilder("SELECT * FROM data_dict.m_common_belong_city  where city3_name is not null and city4_name is null and city_id >0 ");
		if(!StringUtils.isBlank(city1) && !"null".equals(city1)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city1_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		if(!StringUtils.isBlank(city2) && !"null".equals(city2)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}	
		
		if(!StringUtils.isBlank(city3) && !"null".equals(city3)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city3_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}	
		
		sql.append(" group by city3_name ");
		sql.append("order by city_name_en ");	
		return comDao.getAllColsByVo(sql.toString(), BelongLocal.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<BelongLocal> getBelongCitysByOpers(String operCitys) throws Exception {
		//全部城市权限
		if(MyStore.cityOperAll.equals(operCitys)){
			return getBelongLevelOne(null);
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from data_dict.m_common_belong_city  ");
		sql.append(" where city_id > 0 ");
		sql.append(" and city2_name is null ");
		//直营城市权限
		if(MyStore.cityOperZY.equals(operCitys)){
			sql.append(" and city_name in("+ MyStore.zycitys + ") ");
		}else if(MyStore.cityOperQD.equals(operCitys)){//所有渠道城市
			sql.append(" and city_name not in("+ MyStore.zycitys + ") ");
		}else{
			operCitys = operCitys.substring(0,operCitys.lastIndexOf(","));
			sql.append(" and city_name in("+ operCitys + ") ");
		}
		sql.append(" group by city_name_en ");
		sql.append(" order by city_name_en ");	
		
		return comDao.getAllColsByVo(sql.toString(), BelongLocal.class, DBConfig.db_131_data_bi.getIntType());
	
	}
	
	/*
	 * 展现城市
	 */
	public List<DisplayLocal> getDisplayLevelOne(String city1) throws Exception {
		StringBuilder sql = new StringBuilder("select * from data_dict.m_common_disp_city " +
		"where city2_name is null and city_id >0 ");
		if(!StringUtils.isBlank(city1) && !"null".equals(city1)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city1_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		sql.append(" group by city_name_en ");	
		sql.append(" order by city_name_en ");	
		return comDao.getAllColsByVo(sql.toString(), DisplayLocal.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	public List<DisplayLocal> getDisplayLevelTwo(String city1, String city2) throws Exception {
		StringBuilder sql = new StringBuilder("SELECT * FROM data_dict.m_common_disp_city  where city2_name is not null and city3_name is null");
		if(!StringUtils.isBlank(city1) && !"null".equals(city1)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city1_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		if(!StringUtils.isBlank(city2) && !"null".equals(city2)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}	
		sql.append(" group by city_name_en ");
		sql.append("order by city_name_en ");	
		return comDao.getAllColsByVo(sql.toString(), DisplayLocal.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	public List<DisplayLocal> getDisplayLevelThree(String city1,
			String city2 ,String city3) throws Exception {
		StringBuilder sql = new StringBuilder("SELECT * FROM data_dict.m_common_disp_city  where city3_name is not null and city4_name is null and city_id >0 ");
		if(!StringUtils.isBlank(city1) && !"null".equals(city1)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city1_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}
		if(!StringUtils.isBlank(city2) && !"null".equals(city2)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city2_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}	
		
		if(!StringUtils.isBlank(city3) && !"null".equals(city3)){
			String cateTwoArr[] =  city1.split(",");
			sql.append(" and  city3_name  in('" + StringUtils.join(cateTwoArr, "','")+"') " );
		}	
		sql.append(" group by city_name_en ");
		sql.append("order by city_name_en ");	
		return comDao.getAllColsByVo(sql.toString(), DisplayLocal.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	public List<DisplayLocal> getDisplayCitysByOpers(String operCitys) throws Exception {
		//全部城市权限
		if(MyStore.cityOperAll.equals(operCitys)){
			return getDisplayLevelOne(null);
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from data_dict.m_common_disp_city  ");
		sql.append(" where city_id > 0 ");
		sql.append(" and city2_name is null ");
		//直营城市权限
		if(MyStore.cityOperZY.equals(operCitys)){
			sql.append(" and city_name in("+ MyStore.zycitys + ") ");
		}else if(MyStore.cityOperQD.equals(operCitys)){//所有渠道城市
			sql.append(" and city_name not in("+ MyStore.zycitys + ") ");
		}else{
			operCitys = operCitys.substring(0,operCitys.lastIndexOf(","));
			sql.append(" and city_name in("+ operCitys + ") ");
		}
		sql.append(" group by city_name_en ");
		sql.append(" order by city_name_en ");	
		
		return comDao.getAllColsByVo(sql.toString(), DisplayLocal.class, DBConfig.db_131_data_bi.getIntType());
		
	}

	/**
	 * 信息来源
	 * @throws Exception 
	 */
	public List<InfoSourcePlatform> getInfoPlatformList(String infoPlatform) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT *  FROM data_dict.m_common_source_platform where platform is not null ");
		if(!StringUtils.isBlank(infoPlatform) && !"null".equals(infoPlatform)){
			String arr[] =  infoPlatform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		sql.append(" group by platform");
		return comDao.getAllColsByVo(sql.toString(), InfoSourcePlatform.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	public List<InfoSourcePlatform> getInfoSourceList(String infoPlatform,String sourceName) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT *  FROM data_dict.m_common_source_platform where source_name is not null ");
		if(!StringUtils.isBlank(infoPlatform) && !"null".equals(infoPlatform)){
			String arr[] =  infoPlatform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(sourceName) && !"null".equals(sourceName)){
			String arr[] =  sourceName.split(",");
			sql.append(" and  source_name  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		sql.append(" group by source_name");

		return comDao.getAllColsByVo(sql.toString(), InfoSourcePlatform.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	/**
	 * 流量来源
	 */
	public List<NetFlowSource> getSource1List(String platform,String source1) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.t_common_netflow_source_bi where source1 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" group by source1");
		
		return comDao.getAllColsByVo(sql.toString(), NetFlowSource.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<NetFlowSource> getSource2List(String platform,String source1 , String source2)
			throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.t_common_netflow_source_bi where source2 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" group by source2");
		return comDao.getAllColsByVo(sql.toString(), NetFlowSource.class, DBConfig.db_131_data_bi.getIntType());

	}

	public List<NetFlowSource> getSource3List(String platform,String source1, String source2, String source3) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.t_common_netflow_source_bi where source3 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source3) && !"null".equals(source3)){
			String arr[] =  source3.split(",");
			sql.append(" and  source3  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append("  group by source3");
		
		return comDao.getAllColsByVo(sql.toString(), NetFlowSource.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<NetFlowSource> getSource4List(String source1, String source2, String source3, String source4) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.t_common_netflow_source_bi where source4 is not null ");
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source3) && !"null".equals(source3)){
			String arr[] =  source3.split(",");
			sql.append(" and  source3  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source4) && !"null".equals(source4)){
			String arr[] =  source4.split(",");
			sql.append(" and  source4  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append("  group by source4");

		
		return comDao.getAllColsByVo(sql.toString(), NetFlowSource.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	/**
	 * 中华英才来源
	 */
	public List<ChinahrSource> getCHRSource1(String platform,String source1) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_source where source1 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" GROUP BY source1 ");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrSource.class, DBConfig.db_131_data_bi.getIntType());
	}
	
	public List<ChinahrSource> getCHRSource2(String platform, String source1, String source2) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_source where source2 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" group by source2");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrSource.class, DBConfig.db_131_data_bi.getIntType());
	
	}
	
	public List<ChinahrSource> getCHRSource3(String platform, String source1, String source2, String source3) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_source where source3 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source3) && !"null".equals(source3)){
			String arr[] =  source3.split(",");
			sql.append(" and  source3  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append("  group by source3");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrSource.class, DBConfig.db_131_data_bi.getIntType());
		
	}
	
	public List<ChinahrSource> getCHRSource4(String platform, String source1, String source2, String source3, String source4) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_source where source4 is not null ");
		if(!StringUtils.isBlank(platform) && !"null".equals(platform)){
			String arr[] =  platform.split(",");
			sql.append(" and  platform  in('" + StringUtils.join(arr, "','")+"') " );
		}	
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source3) && !"null".equals(source3)){
			String arr[] =  source3.split(",");
			sql.append(" and  source3  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source4) && !"null".equals(source4)){
			String arr[] =  source4.split(",");
			sql.append(" and  source4  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append("  group by source4");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrSource.class, DBConfig.db_131_data_bi.getIntType());
		
	}

	/**
	 * 中华英才职位
	 */
	public List<ChinahrJob> getCHRJobCate1(String cate1_name) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_job where cate1_id >0 ");
		if(!StringUtils.isBlank(cate1_name) && !"null".equals(cate1_name)){
			String arr[] =  cate1_name.split(",");
			sql.append(" and  cate1_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" GROUP BY cate1_name ");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrJob.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<ChinahrJob> getCHRJobCate2(String cate1_name, String cate2_name) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_job where cate2_id >0 ");
		if(!StringUtils.isBlank(cate1_name) && !"null".equals(cate1_name)){
			String arr[] =  cate1_name.split(",");
			sql.append(" and  cate1_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(cate2_name) && !"null".equals(cate2_name)){
			String arr[] =  cate2_name.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" GROUP BY cate2_name ");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrJob.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<ChinahrJob> getCHRJobCate3(String cate1_name, String cate2_name, String cate3_name)
			throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_job where cate3_id >0 ");
		if(!StringUtils.isBlank(cate1_name) && !"null".equals(cate1_name)){
			String arr[] =  cate1_name.split(",");
			sql.append(" and  cate1_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(cate2_name) && !"null".equals(cate2_name)){
			String arr[] =  cate2_name.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(cate3_name) && !"null".equals(cate3_name)){
			String arr[] =  cate3_name.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" GROUP BY cate3_name ");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrJob.class, DBConfig.db_131_data_bi.getIntType());
	}

	/**
	 * 中华英才行业
	 */
	public List<ChinahrIndustry> getCHRIndustryCate1(String cate1_name) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_industry where cate1_id >0 ");
		if(!StringUtils.isBlank(cate1_name) && !"null".equals(cate1_name)){
			String arr[] =  cate1_name.split(",");
			sql.append(" and  cate1_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" GROUP BY cate1_name ");
		return comDao.getAllColsByVo(sql.toString(), ChinahrIndustry.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<ChinahrIndustry> getgetCHRIndustryCate2(String cate1_name, String cate2_name) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.m_common_chinahr_industry where cate2_id >0 ");
		if(!StringUtils.isBlank(cate1_name) && !"null".equals(cate1_name)){
			String arr[] =  cate1_name.split(",");
			sql.append(" and  cate1_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(cate2_name) && !"null".equals(cate2_name)){
			String arr[] =  cate2_name.split(",");
			sql.append(" and  cate2_name  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" GROUP BY cate2_name ");
		
		return comDao.getAllColsByVo(sql.toString(), ChinahrIndustry.class, DBConfig.db_131_data_bi.getIntType());
	
	
	}
	
	/**
	 * 驾校一点通来源
	 */
	public List<JxedtFlowSource> getJxedtSource1List(String platform,String source1) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.jxedt_netflow_source where platform = '"+platform+"' and source1 is not null");
		
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" group by source1 ");
		
		return comDao.getAllColsByVo(sql.toString(), JxedtFlowSource.class, DBConfig.db_131_data_bi.getIntType());
	}

	public List<JxedtFlowSource> getJxedtSource2List(String platform,String source1,String source2)
			throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM data_dict.jxedt_netflow_source where platform = '"+platform+"' and source1 is not null");
		
		if(!StringUtils.isBlank(source1) && !"null".equals(source1)){
			String arr[] =  source1.split(",");
			sql.append(" and  source1  in('" + StringUtils.join(arr, "','")+"') " );
		}
		if(!StringUtils.isBlank(source2) && !"null".equals(source2)){
			String arr[] =  source2.split(",");
			sql.append(" and  source2  in('" + StringUtils.join(arr, "','")+"') " );
		}
		sql.append(" group by source2 ");
		
		return comDao.getAllColsByVo(sql.toString(), JxedtFlowSource.class, DBConfig.db_131_data_bi.getIntType());
	}
}
